import pandas as pd
import os
import glob
import re
import time
from program.pack.append_df_to_excel import append_df_to_excel

import numpy as np
import warnings

# 忽略警告
warnings.filterwarnings("ignore")

# 列显示不全，进行设置
pd.set_option('display.max_columns', 500)
pd.set_option('display.unicode.ambiguous_as_wide', True)
pd.set_option('display.unicode.east_asian_width', True)
pd.set_option('display.width', 180)  # 设置打印宽度(**重要**)


# 做统计计算  ‘合计’
def func_heji(data):
    # 总计
    k_0 = data['测试组数'].sum()
    k_1 = data['检出次数'].sum()
    k_2 = data['误报次数'].sum()
    k_3 = data['可见次数'].sum()
    # 检出率
    k_4 = data['检出次数'].sum() / data['测试组数'].sum()
    k_4 = "%.2f%%" % (k_4 * 100)
    # 误报率
    k_5 = data['误报次数'].sum() / data['测试组数'].sum()
    k_5 = "%.2f%%" % (k_5 * 100)
    # 可见率
    k_6 = data['可见次数'].sum() / data['测试组数'].sum()
    k_6 = "%.2f%%" % (k_6 * 100)
    data.sort_index(inplace=True)  # 按索引排序
    # 进行合计报告
    row = {'测试组数': k_0, '检出次数': k_1, '误报次数': k_2, '可见次数': k_3, '检出率': k_4, '误报率': k_5, '可见率': k_6}
    data.loc['合计'] = row
    data = data.reset_index()
    return data


# 按mod_name进行聚合统计，按各mod_name统计 测试组数，检出次数, 可见次数, 误报次数
def func_group(data, mod_name='BMI体质'):
    data_group = data.groupby([mod_name]).agg({'是否检出': ['count', 'sum'],
                                                           '是否可见': ['sum'],
                                                           '是否误报': ['sum']})
    data_group.columns = ['测试组数', '检出次数', '可见次数', '误报次数']

    data_group['检出率'] = data_group['检出次数'] / data_group['测试组数']
    data_group['可见率'] = data_group['可见次数'] / data_group['测试组数']
    data_group['误报率'] = data_group['误报次数'] / data_group['测试组数']

    data_group['检出率'] = data_group['检出率'].apply(lambda x: format(x, '.2%'))
    data_group['可见率'] = data_group['可见率'].apply(lambda x: format(x, '.2%'))
    data_group['误报率'] = data_group['误报率'].apply(lambda x: format(x, '.2%'))
    # data_group = data_group.reset_index()
    return data_group


def buwei_sorted(data, sorted_by='部位编号'):
    # 部位示例 方便填充数据 部位编号: 身体部位
    data_01 = pd.read_excel(r'..\\辅助样例\部位示例.xlsx')
    list1 = data_01[sorted_by].to_list()
    list1 = sorted(set(list1), key=list1.index)

    list2 = data[sorted_by].to_list()
    list2 = sorted(set(list2), key=list1.index)

    # 判断排序（部位）是否是部位示例子集
    if set(list2).issubset(set(list1)):
        try:
            list_custom = list2
            data[sorted_by] = data[sorted_by].astype('category')
            # inplace = True，使 recorder_categories生效
            data[sorted_by].cat.reorder_categories(list_custom, inplace=True)
            # inplace = True，使 df生效
            data.sort_values(sorted_by, inplace=True)
            return data
        except Exception as e:
            print('数据输出错误', e)
            return data


# ----------------1、按BMI---------------
def main_bmi(path_out):
    try:
        mod_name = 'BMI体质'
        data_10 = func_group(data_0, mod_name)
        data_10 = func_heji(data_10)
        append_df_to_excel(path_out, data_10, sheet_name=mod_name, startcol=1,
                           startrow=1, index=False)  # 第1列 第0行
        if data_1 is not None:
            data_11 = func_group(data_1, mod_name)
            data_11 = func_heji(data_11)
            append_df_to_excel(path_out, data_11, sheet_name=mod_name, startcol=data_10.shape[1] + 2,
                               startrow=1, index=False)
        else:
            pass
        print(mod_name, '数据输出成功')
    except Exception as e:
        print(mod_name, '数据输出错误', e)


# ----------------1、按BMI---------------

# ----------------2、按性别进行数据分析----------------
def func_sorted_sex(data):
    try:
        list_custom = ['男', '女', '合计']
        data['性别'] = data['性别'].astype('category')
        # inplace = True，使 recorder_categories生效
        data['性别'].cat.reorder_categories(list_custom, inplace=True)
        # inplace = True，使 df生效
        data.sort_values('性别', inplace=True)
        return data
    except Exception as e:
        return data
        print('数据输出错误', e)


def main_sex(path_out):
    try:
        mod_name = '性别'
        data_10 = func_group(data_0, mod_name)
        data_10 = func_heji(data_10)
        data_10 = func_sorted_sex(data_10)
        append_df_to_excel(path_out, data_10, sheet_name=mod_name, startcol=1,
                           startrow=1, index=False)  # 第1列 第0行
        if data_1 is not None:
            data_11 = func_group(data_1, mod_name)
            data_11 = func_heji(data_11)
            data_11 = func_sorted_sex(data_11)
            append_df_to_excel(path_out, data_11, sheet_name=mod_name, startcol=data_10.shape[1] + 2,
                               startrow=1, index=False)
        else:
            pass
        print(mod_name, '数据输出成功')
    except Exception as e:
        print(mod_name, '数据输出错误', e)


# ----------------2、按性别----------------

# ----------------3、按样品进行数据分析----------------
def main_sample(path_out):
    for sample_name in ['样品大类', '样品名称', '样品']:
        mod_name = sample_name
        try:
            data_10 = func_group(data_0, mod_name)
            data_10 = func_heji(data_10)
            append_df_to_excel(path_out, data_10, sheet_name=mod_name, startcol=1,
                               startrow=1, index=False)  # 第1列 第0行
            if data_1 is not None:
                data_11 = func_group(data_1, mod_name)
                data_11 = func_heji(data_11)
                append_df_to_excel(path_out, data_11, sheet_name=mod_name, startcol=data_10.shape[1] + 2,
                                   startrow=1, index=False)
            else:
                pass
            print(mod_name, '数据输出成功')
        except Exception as e:
            print(mod_name, '数据输出错误', e)


# ----------------3、按样品----------------

# ----------------4、按衣物季节进行数据分析----------------
def main_season(path_out):
    try:
        mod_name = '衣物季节'
        data_10 = func_group(data_0, mod_name)
        data_10 = func_heji(data_10)
        append_df_to_excel(path_out, data_10, sheet_name=mod_name, startcol=1,
                           startrow=1, index=False)  # 第1列 第0行
        if data_1 is not None:
            data_11 = func_group(data_1, mod_name)
            data_11 = func_heji(data_11)
            append_df_to_excel(path_out, data_11, sheet_name=mod_name, startcol=data_10.shape[1] + 2,
                               startrow=1, index=False)
        else:
            pass
        print(mod_name, '数据输出成功')
    except Exception as e:
        print(mod_name, '数据输出错误', e)


# ----------------4、按衣物季节----------------

# ----------------5、按文件进行数据分析---------------

# 按文件名进行聚合统计，
def func_52(data):
    list_file = ['文件名', '样品', '姓名', '性别', 'BMI体质', '衣物季节', '工作人员', '样品大类', '样品名称']
    data_group = data.groupby(list_file).agg \
        ({'是否检出': ['count', 'sum'],
          '是否可见': ['sum'],
          '是否误报': ['sum']})
    data_group.columns = ['测试组数', '检出次数', '可见次数', '误报次数']

    data_group['检出率'] = data_group['检出次数'] / data_group['测试组数']
    data_group['可见率'] = data_group['可见次数'] / data_group['测试组数']
    data_group['误报率'] = data_group['误报次数'] / data_group['测试组数']

    data_group['检出率'] = data_group['检出率'].apply(lambda x: format(x, '.2%'))
    data_group['可见率'] = data_group['可见率'].apply(lambda x: format(x, '.2%'))
    data_group['误报率'] = data_group['误报率'].apply(lambda x: format(x, '.2%'))
    data_group = data_group.reset_index()
    # 总计
    k_0 = data_group['测试组数'].sum()
    k_1 = data_group['检出次数'].sum()
    k_2 = data_group['误报次数'].sum()
    k_3 = data_group['可见次数'].sum()
    # 检出率
    k_4 = data_group['检出次数'].sum() / data_group['测试组数'].sum()
    k_4 = "%.2f%%" % (k_4 * 100)
    # 误报率
    k_5 = data_group['误报次数'].sum() / data_group['测试组数'].sum()
    k_5 = "%.2f%%" % (k_5 * 100)
    # 可见率
    k_6 = data_group['可见次数'].sum() / data_group['测试组数'].sum()
    k_6 = "%.2f%%" % (k_6 * 100)
    data_group.sort_index(inplace=True)  # 按索引排序
    # 进行合计报告
    row = {'测试组数': k_0, '检出次数': k_1, '误报次数': k_2, '可见次数': k_3, '检出率': k_4, '误报率': k_5, '可见率': k_6}
    data_group.loc['合计'] = row
    data_group['文件名'].fillna('合计', inplace=True)

    k_columns = ['文件名', '姓名', '性别', 'BMI体质', '衣物季节', '工作人员', '样品大类', '样品名称',
                 '样品', '测试组数', '检出次数', '可见次数', '误报次数', '检出率', '可见率', '误报率', ]
    data_group = data_group[k_columns]
    return data_group


def main_file(path_out):
    try:
        mod_name = '文件名'
        data_50 = func_52(data_0)
        append_df_to_excel(path_out, data_50, sheet_name=mod_name, startcol=1,
                           startrow=1, index=False)  # 第1列 第0行

        if data_1 is not None:
            data_51 = func_52(data_1)
            append_df_to_excel(path_out, data_51, sheet_name=mod_name, startcol=data_50.shape[1] + 2,
                               startrow=1, index=False)  # 第1列 第0行
        else:
            pass
        print(mod_name, '数据输出成功')
    except Exception as e:
        print(mod_name, '数据输出错误', e)


# ----------------5、按文件----------------

# ----------------6、误报部位---------------
def func_60(data):
    # 部位示例 方便填充数据 部位编号: 身体部位
    data.dropna(axis=0, subset=['备注'], how='any', inplace=True)
    list_0 = data['备注'].replace(' ', '', regex=True)
    data.dropna(axis=0, subset=['备注_1'], how='any', inplace=True)
    list_1 = data['备注_1'].replace(' ', '', regex=True)
    data.dropna(axis=0, subset=['备注_2'], how='any', inplace=True)
    list_2 = data['备注_2'].replace(' ', '', regex=True)
    data.dropna(axis=0, subset=['备注_3'], how='any', inplace=True)
    list_3 = data['备注_3'].replace(' ', '', regex=True)
    data.dropna(axis=0, subset=['备注_4'], how='any', inplace=True)
    list_4 = data['备注_4'].replace(' ', '', regex=True)
    # data.dropna(axis=0, subset=['备注_6'], how='any', inplace=True)
    # list_4 = data['备注_6'].replace(' ', '', regex=True)
    list_0 = list(list_0)
    list_1 = list(list_1)
    list_2 = list(list_2)
    list_3 = list(list_3)
    list_4 = list(list_4)
    list0 = list_0 + list_1 + list_2 + list_3 + list_4
    # 大小写格式转换
    list0 = [item.upper() for item in list0]
    # 去除不合规的部位编号
    data_01 = pd.read_excel(r'..\\辅助样例\部位示例.xlsx')
    list_buwei = list(data_01['部位编号'])
    list0 = [i for i in list0 if i in list_buwei]
    # 打印不合格的’误报部位‘
    list1 = [i for i in list0 if i not in list_buwei]
    print(list1)
    # 转化为DataFrame
    data = pd.DataFrame({'部位编号': list0})
    data.loc[data['部位编号'] == 'JBL', '部位编号'] = 'B4L'

    data_1 = data.groupby(['部位编号']).size()
    data_2 = data.groupby(['部位编号']).sum()
    data = pd.concat([data_1, data_2], axis=1)
    data['误报次数'] = data[0]
    data = data.reset_index()
    # data = data.sort_values(by='误报次数', ascending=False)
    # 丢弃索引
    data = data.reset_index()

    # 重新整理列的顺序
    k_columns = ['部位编号', '误报次数']
    data = data[k_columns]
    return data


# 按部位进行聚合统计，按各部位统计 测试组数，检出次数, 可见次数, 误报次数
def func_61(data):
    data_1 = data[['身体大类', '身体部位', '部位编号', '是否检出', '是否可见', '是否误报']]
    data_wubao = func_60(data)
    data_2_1 = data_1.groupby(['部位编号']).size()
    data_2_2 = data_1.groupby(['部位编号']).sum()

    data_3 = pd.concat([data_2_1, data_2_2], axis=1)
    data_3['测试组数'] = data_3[0]
    data_3 = data_3[['是否检出', '是否可见', '是否误报', '测试组数']]
    data_3['测试组数'] = data_3['测试组数']
    data_3['检出次数'] = data_3['是否检出']
    data_3['可见次数'] = data_3['是否可见']

    data_3 = data_3.reset_index()
    data_3 = pd.merge(data_3, data_wubao, how='outer', on=['部位编号'])
    data_3 = data_3.set_index('部位编号')
    # data_3['误报次数'] = 0
    data_3['误报次数'] = data_3['误报次数'].fillna(0)
    data_3['误报次数'] = data_3['误报次数'].astype(int)

    data_3 = data_3[['测试组数', '检出次数', '误报次数', '可见次数']]
    data_3['检出率'] = data_3['检出次数'] / data_3['测试组数']
    # data_3 = data_3.sort_values(by='检出率')
    data_3['检出率'] = data_3['检出率'].apply(lambda x: format(x, '.2%'))
    data_3['可见率'] = data_3['可见次数'] / data_3['测试组数']
    data_3['可见率'] = data_3['可见率'].apply(lambda x: format(x, '.2%'))
    data_3['误报率'] = data_3['误报次数'] / data_3['测试组数'].sum()
    data_3['误报率'] = data_3['误报率'].apply(lambda x: format(x, '.2%'))
    return data_3


# 对部位编号进行遍历，并重新填充新列
def func_62(data):
    # 计算有多少行
    data_buwei = pd.read_excel(r'..\\辅助样例\部位示例.xlsx')
    len_1 = len(list(data_buwei['部位编号']))
    for i in range(len_1):
        k_0 = data_buwei.values[i][0]
        k_1 = data_buwei.values[i][1]
        k_2 = data_buwei.values[i][2]

        data.loc[data['部位编号'] == k_2, '身体大类'] = k_0
        data.loc[data['部位编号'] == k_2, '身体部位'] = k_1

        # 按 '部位编号' 进行排序
        # data.sort_values(by=['部位编号'], ascending=True, inplace=True)

        # 丢弃索引
        data = data.reset_index()

        # 重新整理列的顺序
        k_columns = ['身体大类', '身体部位', '部位编号',
                     '测试组数', '检出次数', '误报次数', '可见次数', '检出率', '误报率', '可见率']
        data = data[k_columns]
    data[['测试组数', '检出次数', '误报次数', '可见次数']] = data[['测试组数', '检出次数', '误报次数', '可见次数']].fillna(int(0))
    data = data.fillna('合计')

    return data


def func_63(data):
    data_1 = data.groupby(['身体大类']).size()
    data_2 = data.groupby(['身体大类']).sum()

    data_3 = pd.concat([data_1, data_2], axis=1)

    data_3 = data_3[['测试组数', '检出次数', '误报次数', '可见次数']]
    data_3['检出率'] = data_3['检出次数'] / data_3['测试组数']
    data_3['检出率'] = data_3['检出率'].apply(lambda x: format(x, '.2%'))
    data_3['可见率'] = data_3['可见次数'] / data_3['测试组数']
    data_3['可见率'] = data_3['可见率'].apply(lambda x: format(x, '.2%'))
    data_3['误报率'] = data_3['误报次数'] / data_3['测试组数']
    data_3['误报率'] = data_3['误报率'].apply(lambda x: format(x, '.2%'))

    data_3 = data_3.reset_index()
    data_3 = data_3[['身体大类', '测试组数', '检出次数', '误报次数', '可见次数', '检出率', '误报率', '可见率']]
    # fs_0 = dfs_0.drop(dfs_0[dfs_0['是否可见'].isnull()].index)
    data_3 = pd.concat([data_3, data_3[data_3['身体大类'] == '合计']], ignore_index=True)
    data_3 = data_3.drop_duplicates(keep='last')
    return data_3


def main_body(path_out):
    try:
        data_60 = data_0
        data_60 = func_61(data_60)
        data_60 = func_heji(data_60)
        data_60 = func_62(data_60)
        data_60 = buwei_sorted(data_60, sorted_by='部位编号')

        append_df_to_excel(path_out, data_60, sheet_name='细分部位', startcol=1,
                           startrow=1, index=False)
        data_600 = func_63(data_60)
        data_600 = buwei_sorted(data_600, sorted_by='身体大类')
        append_df_to_excel(path_out, data_600, sheet_name='身体九大类', startcol=1,
                           startrow=1, index=False)
        if data_1 is not None:
            data_61 = data_1
            data_61 = func_61(data_61)
            data_61 = func_heji(data_61)
            data_61 = func_62(data_61)
            data_61 = buwei_sorted(data_61, sorted_by='部位编号')
            append_df_to_excel(path_out, data_61, sheet_name='细分部位', startcol=data_60.shape[1] + 2,
                               startrow=1, index=False)
            data_610 = func_63(data_61)
            data_610 = buwei_sorted(data_610, sorted_by='身体大类')
            append_df_to_excel(path_out, data_610, sheet_name='身体九大类', startcol=data_610.shape[1] + 2,
                               startrow=1, index=False)
        else:
            pass
        print('部位 数据输出成功')
        return data_60
    except Exception as e:
        print('部位 数据输出错误', e)


# ----------------6、按部位---------------

# ----------------按部位-样品  查看测试组数（检出率、误报率、可见率、其他）---------------
def sample_body(path_out, sheet_name='原始数据', cmo='检出率'):
    pro_0 = '身体部位'
    pro_1 = '样品名称'
    cmo_0 = '是否' + cmo.rstrip('率')
    data_sample = pd.read_excel(path_out, sheet_name=sheet_name)
    list1 = data_sample[pro_0].to_list()
    list1 = sorted(set(list1), key=list1.index)

    list2 = data_sample[pro_1].to_list()
    list2 = sorted(set(list2), key=list2.index)

    df = pd.DataFrame()
    df[pro_0] = list1
    df[list2] = None
    for i in range(len(list2)):
        for j in range(len(list1)):
            df.loc[j, list2[i]] = data_0[cmo_0][(data_0[pro_1] == list2[i]) & (data_0[pro_0] == list1[j])].mean()

    for j in range(len(list1)):
        df.loc[j, '合计'] = data_0[cmo_0][(data_0[pro_0] == list1[j])].mean()

    for i in range(len(list2)):
        df.loc[pro_0, list2[i]] = data_0[cmo_0][(data_0[pro_1] == list2[i])].mean()
        df[list2[i]] = df[list2[i]].apply(lambda x: format(x, '.2%'))
    df['合计'] = df['合计'].apply(lambda x: format(x, '.2%'))
    df[pro_0] = df[pro_0].fillna('合计')

    df.values[-1][-1] = "%.2f%%" % (data_0[cmo_0].mean() * 100)  # 最后一个数据填充
    df = buwei_sorted(df, sorted_by=pro_0)
    df = df.replace('nan%', np.nan, regex=True)
    append_df_to_excel(path_out, df, sheet_name='部位-样品 ' + cmo, startcol=0,
                       startrow=0, index=False)
    print('部位-样品 ' + cmo + '输出成功')
    # -------------------------
    df_1 = pd.DataFrame()
    df_1['身体部位'] = list1
    for i in range(len(list2)):
        for j in range(len(list1)):
            df_1.loc[j, list2[i]] = data_0[cmo_0][(data_0[pro_1] == list2[i]) & (data_0[pro_0] == list1[j])].count()
    df_1 = df_1.set_index('身体部位')
    df_1["合计"] = df_1.apply(lambda x: x.sum(), axis=1)
    df_1.loc["合计"] = df_1.apply(lambda x: x.sum())
    df_1 = df_1.reset_index()
    append_df_to_excel(path_out, df_1, sheet_name='部位-样品 测试组数', startcol=0,
                       startrow=0, index=False)


# 文件输入位置
paths = r'W:\wangshuan\TAI40-II\TAI40-II_vs\TAI40-II对比测试.xlsx'
data_1 = None

data_0 = pd.read_excel(paths, sheet_name='data')
data_0['是否可见'] = data_0['人工可见']
data_0 = data_0[(data_0['机器编号'] != '1#') ]

data_1 = pd.read_excel(paths, sheet_name='data')
data_1['是否可见'] = data_1['人工可见']
data_1 = data_1[(data_1['机器编号'] != '4#') ]
# 文件输出位置
path_out = paths

# 可以进行简单筛选
data_0 = data_0[(data_0['文件名'] != 'test.xlsx') ]

if __name__ == "__main__":
    start = time.time()
    # sample_body(path_out, cmo='检出率', sheet_name='data')  # 部位-样品 检出率
    # sample_body(path_out, cmo='误报率', sheet_name='data')  # 部位-样品 误报率
    # sample_body(path_out, cmo='可见率', sheet_name='data')  # 部位-样品 可见率
    main_bmi(path_out)  # BMI
    main_sex(path_out)  # 性别
    main_season(path_out)  # 季节衣物
    main_sample(path_out)  # 样品
    main_file(path_out)  # 文件
    main_body(path_out)  # 身体部位，身体大类
    # time.sleep(2)
    # 记录时间
    end = time.time()
    print("代码运行耗时{:.2f}秒".format(end - start))
